IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_SiteAdminMapSelectAll]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_SiteAdminMapSelectAll]
GO
CREATE procedure [dbo].[cms_sp_SiteAdminMapSelectAll]
as
select 
	SiteAdminMaps.SiteAdminMapId as Id,
	parent.SiteAdminMapId as ParentNodeId,
	cast(SiteAdminMaps.NodeTreeId.GetLevel() as int) as Level,
	SiteAdminMaps.NodeTitle,
	SiteAdminMaps.NodeDescription,
	SiteAdminMaps.NodeUrl,
	SiteAdminMaps.IsSpecial,
	SiteAdminMaps.IsVisible,
	SiteAdminMaps.IsCustom,	
	(select case
		when COUNT(child.SiteAdminMapId) > 0 then 1
			else 0
		end 
	from [dbo].[SiteAdminMaps] as child
		where child.NodeTreeId.GetAncestor(1) = SiteAdminMaps.NodeTreeId) as hasChildren
from [dbo].[SiteAdminMaps] 
left join [dbo].[SiteAdminMaps] as parent
on parent.NodeTreeId = SiteAdminMaps.NodeTreeId.GetAncestor(1)
order by SiteAdminMaps.NodeTreeId


GO

